package com.kma.ncpractice2013.dao;

import com.kma.ncpractice2013.model.User;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.simple.ParameterizedRowMapper;
import java.sql.Date;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/**
 * @author Illya
 */

public class UserDAO extends DAO<User>
{

	private static final String INSERT = "INSERT INTO USERS(LOGIN,PASSWORD,SALT,NAME,SURNAME,BIRTHDATE,PHONE," +
            "REGISTRATION_DATE,GROUP_ID,BLOCKED,ACCESS_LEVEL_ID,REGISTRATION_TOKEN, BONUS, BONUS_TOTAL)" +
            "VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
    private static final String UPDATE = "UPDATE USERS SET LOGIN = ?, PASSWORD = ?, SALT = ?, NAME = ?,"
            + "SURNAME = ?, BIRTHDATE = ?, PHONE = ?, REGISTRATION_DATE = ?, " +
            "GROUP_ID = ?, BLOCKED = ?, ACCESS_LEVEL_ID = ?, REGISTRATION_TOKEN = ?, PASSWD_RESET_TOKEN = ?, BONUS = ?, BONUS_TOTAL = ? WHERE ID = ?";
    private static final String SELECT_BY_ID = "SELECT * FROM USERS WHERE ID = ?";
    private static final String SELECT_BY_LOGIN = "SELECT * FROM USERS WHERE LOGIN = ?";
    private static final String SELECT_BY_TOKEN = "SELECT * FROM USERS WHERE REGISTRATION_TOKEN = ?";
    private static final String SELECT_BY_LOGIN_PASSWORD = "SELECT * FROM USERS WHERE LOGIN = ? AND PASSWORD = ?";
    private static final String SELECT_ALL = "SELECT * FROM USERS";

    public void create(User user)
    {
        String sql = INSERT;
        jdbcTemplate.update(sql, user.getLogin(), user.getPassword(),
                            user.getSalt(), user.getName(), user.getSurname(), user.getBirthdate(), user.getPhone(),
                            user.getRegistrationDate(), user.getGroupId().getValue(), user.getBlocked(), user.getAccessLevelId().getValue(),
                            user.getRegistrationToken(), user.getBonus(), user.getTotalBonus());
    }

    public void update(User user)
    {
        String sql = UPDATE;
        jdbcTemplate.update(sql, user.getLogin(), user.getPassword(),
                            user.getSalt(), user.getName(), user.getSurname(), user.getBirthdate(), user.getPhone(),
                            user.getRegistrationDate(), user.getGroupId().getValue(), user.getBlocked(),
                            user.getAccessLevelId().getValue(), user.getRegistrationToken(), user.getPasswdResetToken(), user.getBonus(), user.getTotalBonus(), user.getId());
    }

    // TODO: create something like getBy(String key, String value)

    public User getByResetToken(String token)
    {
        List<User> userList;
        String sql = "select * from users where passwd_reset_token = ?";
        userList =  jdbcTemplate.query(sql, new UserRowMapper(), token);
        return userList.get(0);
    }

    public User getById(int id)
    {
        List<User> userList;
        String sql = SELECT_BY_ID;
        userList = jdbcTemplate.query(sql, new UserRowMapper(), id);
        return (userList == null || userList.size() == 0) ? null : userList.get(0);
    }

    public void updateBy(int id, String field, String value)        //only for string fields
    {
        String sql = "UPDATE USERS SET "+field+" = ? where id = ?";
        jdbcTemplate.update(sql, value, id);
    }

    public User getByLogin(String login)
    {
        List<User> userList;
        String sql = SELECT_BY_LOGIN;
		JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
	    userList = jdbcTemplate.query(sql, new UserRowMapper(), login);
        return (userList == null || userList.size() == 0) ? null : userList.get(0);
    }

    public User getByRegistrationToken(String token)
    {
        List<User> userList;
        String sql = SELECT_BY_TOKEN;
        userList = jdbcTemplate.query(sql, new UserRowMapper(), token);
        return (userList == null || userList.size() == 0) ? null : userList.get(0);
    }

    public User getByLogPass(String login, String password)
    {
        List<User> userList;
        String sql = SELECT_BY_LOGIN_PASSWORD;
        userList = jdbcTemplate.query(sql, new UserRowMapper(), login, password);
        return (userList == null || userList.size() == 0) ? null : userList.get(0);
    }

    public List<User> search(String login, String name, String surname, Date startDate, Date endDate, Integer userGroup, Integer blockStatus, int page, int entriesCount)
    {
        List<Object> argsList = new ArrayList<Object>();
        String sql = "SELECT * FROM (SELECT * FROM USERS WHERE 1=1 ";
        if (login!=null)
        {
            sql += "and login = ? ";
            argsList.add(login);
        }
        if (name!=null)
        {
            sql += "and name = ? ";
            argsList.add(name);
        }
        if (surname!=null)
        {
            sql += "and surname = ? ";
            argsList.add(surname);
        }
        if (startDate!=null)
        {
            sql += "and registration_date > ? ";
            argsList.add(startDate);
        }
        if (endDate!=null)
        {
            sql += " and registration_date < ? ";
            argsList.add(endDate);
        }
        if (userGroup != null)
        {
            sql += "and group_id = ? ";
            argsList.add(userGroup);
        }
        if (blockStatus!= null)
        {
            sql += "and blocked = ?";
            argsList.add(blockStatus);
        }
        sql += ") WHERE (ROWNUM <= ?*(?+1) AND ROWNUM >= ?*?)";
        argsList.add(entriesCount);
        argsList.add(page);
        argsList.add(entriesCount);
        argsList.add(page);
        return jdbcTemplate.query(sql, new UserRowMapper(), argsList.toArray());
    }

    // returns the users which are not blocked and which are neither managers nor the administrator
    public List<User> getRequestedUsersPerPage(String login, String name, String surname, User.Group group, int page, int entriesCount)
    {
        List<Object> argsList = new ArrayList<Object>();
        String sql = "SELECT USERS.* FROM (SELECT ROWNUM RW, USERS.* FROM USERS WHERE ROWNUM <= ? * (? + 1) ";
        argsList.add(entriesCount);
        argsList.add(page);
        if (group != null)
        {
            sql += "AND GROUP_ID = ? ";
            argsList.add(group.getValue());
        }
        else
            sql += " AND GROUP_ID > 1 ";
        sql += "AND BLOCKED = 0 AND ACCESS_LEVEL_ID = ?";
        argsList.add(User.AccessLevel.registeredCustomer.getValue());
        if (login != null)
        {
            sql += "AND LOGIN LIKE '%?%' ";
            argsList.add(login);
        }
        if (name != null)
        {
            sql += "AND NAME LIKE '%?%' ";
            argsList.add(name);
        }
        if (surname!=null)
        {
            sql += "AND SURNAME LIKE '%?%' ";
            argsList.add(surname);
        }
        sql += ") USERS WHERE USERS.RW > ? * ?";
        argsList.add(entriesCount);
        argsList.add(page);
        List<User> users = jdbcTemplate.query(sql, new UserRowMapper(), argsList.toArray());
        return (users == null || users.size() == 0) ? null : users;
    }

    public int getRequestedUsersCount(String login, String name, String surname, User.Group group)
    {
        List<Object> argsList = new ArrayList<Object>();
        String sql = "SELECT * FROM USERS WHERE BLOCKED = 0 AND ACCESS_LEVEL_ID = ? ";
        argsList.add(User.AccessLevel.registeredCustomer.getValue());
        if (login != null)
        {
            sql += "AND LOGIN LIKE '%?%' ";
            argsList.add(login);
        }
        if (name != null)
        {
            sql += "AND NAME LIKE '%?%' ";
            argsList.add(name);
        }
        if (surname!=null)
        {
            sql += "AND SURNAME LIKE '%?%' ";
            argsList.add(surname);
        }
        if (group != null)
        {
            sql += "AND GROUP_ID = ? ";
            argsList.add(group.getValue());
        }
        else
            sql += " AND GROUP_ID > 1 ";
        List<User> list = jdbcTemplate.query(sql, new UserRowMapper(), argsList.toArray());
        return (list == null) ? 0 : list.size();
    }

    public List<User> getAll()
    {
        String sql = SELECT_ALL;
        List<User> userList = jdbcTemplate.query(sql, new UserRowMapper());
        return userList;
    }

    public void deleteById(int user_id)
    {
        String sql = "DELETE FROM USERS WHERE ID = ?";
        jdbcTemplate.update(sql, user_id);
    }

    private static final class UserRowMapper implements ParameterizedRowMapper<User>
    {
        @Override
        public User mapRow(ResultSet rs, int rowNum) throws SQLException
        {
            User user = new User();
            user.setId(rs.getInt("id"));
            user.setName(rs.getString("name"));
            user.setSurname(rs.getString("surname"));
            user.setPassword(rs.getString("password"));
            user.setSalt(rs.getString("salt"));
            user.setPhone(rs.getString("phone"));
            user.setRegistrationDate(rs.getDate("registration_date"));
            user.setBirthdate(rs.getDate("birthdate"));
            user.setLogin(rs.getString("login"));
            user.setGroupId(User.Group.getByValue(rs.getInt("group_id")));
            user.setBlocked(rs.getInt("blocked"));
            user.setAccessLevelId(User.AccessLevel.getByValue(rs.getInt("access_level_id")));
            user.setRegistrationToken(rs.getString("registration_token"));
            user.setPasswdResetToken(rs.getString("passwd_reset_token"));
	        user.setBonus(rs.getDouble("bonus"));
	        user.setTotalBonus(rs.getDouble("bonus_total"));
            return user;
        }
    }
}
